# oco9-ai-validator/db_utils.py

import sqlite3
import json
from datetime import datetime, timedelta
import os
import logging

logger = logging.getLogger(__name__)

class DBUtils:
    def __init__(self, db_path: str = "validation_cache.db"):
        self.db_path = db_path
        self._init_database()

    def _init_database(self):
        """初始化SQLite数据库，创建或检查表结构"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # github_repos 表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS github_repos (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                repo_name TEXT NOT NULL,
                repo_url TEXT NOT NULL UNIQUE,
                owner TEXT NOT NULL,
                description TEXT,
                language TEXT,
                stars INTEGER,
                processed_at TEXT DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(owner, repo_name)
            )
        ''')

        # 新增 repo_pypi_packages 表，与 github_repos 形成一对多关系
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS repo_pypi_packages (
                repo_id INTEGER,
                package_name TEXT NOT NULL,
                PRIMARY KEY (repo_id, package_name),
                FOREIGN KEY (repo_id) REFERENCES github_repos(id) ON DELETE CASCADE
            )
        ''')
        
        conn.commit()
        conn.close()
        logger.info(f"数据库已初始化/检查: {self.db_path}")

    def save_repo_data(self, repo_name: str, repo_url: str, owner: str, 
                       description: str, language: str, stars: int, 
                       pip_packages: list):
        """将仓库信息和提取的PyPI包保存到数据库"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        processed_at = datetime.now().isoformat()
        
        try:
            # 插入或替换 github_repos 表数据
            cursor.execute('''
                INSERT OR REPLACE INTO github_repos 
                (repo_name, repo_url, owner, description, language, stars, processed_at)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            ''', (
                repo_name,
                repo_url,
                owner,
                description,
                language,
                stars,
                processed_at
            ))
            
            repo_id = cursor.lastrowid # 获取插入的 repo_id

            # 清除旧的包数据，避免重复
            cursor.execute('DELETE FROM repo_pypi_packages WHERE repo_id = ?', (repo_id,))

            # 插入新的包数据
            if pip_packages:
                package_data = [(repo_id, pkg) for pkg in pip_packages]
                cursor.executemany('''
                    INSERT INTO repo_pypi_packages (repo_id, package_name)
                    VALUES (?, ?)
                ''', package_data)
            
            conn.commit()
            logger.debug(f"已保存/更新到数据库: {repo_name} ({owner}/{repo_name})")
            
        except sqlite3.Error as e:
            logger.error(f"数据库保存失败 {repo_name}: {str(e)}")
        finally:
            conn.close()

    def get_repo_data(self, owner: str, repo_name: str, cache_days: int) -> dict | None:
        """从数据库获取仓库数据，并检查缓存有效期"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            # 获取 repo 信息
            cursor.execute('''
                SELECT id, repo_name, repo_url, owner, description, language, stars, processed_at
                FROM github_repos 
                WHERE owner = ? AND repo_name = ?
            ''', (owner, repo_name))
            
            repo_row = cursor.fetchone()
            if not repo_row:
                return None
            
            repo_id = repo_row[0]
            processed_at_str = repo_row[7]
            
            if processed_at_str:
                processed_time = datetime.fromisoformat(processed_at_str)
                if datetime.now() - processed_time <= timedelta(days=cache_days):
                    logger.debug(f"命中有效缓存: {owner}/{repo_name}")
                    
                    # 获取关联的 PyPI 包
                    cursor.execute('''
                        SELECT package_name FROM repo_pypi_packages WHERE repo_id = ?
                    ''', (repo_id,))
                    pip_packages = [row[0] for row in cursor.fetchall()]

                    return {
                        'repo_name': repo_row[1],
                        'repo_url': repo_row[2],
                        'owner': repo_row[3],
                        'description': repo_row[4],
                        'language': repo_row[5],
                        'stars': repo_row[6],
                        'pip_packages': pip_packages
                    }
                else:
                    logger.info(f"缓存过期: {owner}/{repo_name}")
            return None
            
        except sqlite3.Error as e:
            logger.error(f"数据库查询失败 {owner}/{repo_name}: {str(e)}")
            return None
        finally:
            conn.close()
